# coding:utf-8
import sys, io
from sql_ac.sql_1 import update_dding
import pandas as pd
import xlrd,pymysql
# sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf8')

db = pymysql.connect(host='localhost', user='root', password='root', database='test', charset='utf8')
cursor = db.cursor()

# 遍历文件
rbook = xlrd.open_workbook('要去重的手机号.xls')
# sheets方法返回对象列表,[<xlrd.sheet.Sheet object at 0x103f147f0>]
rbook.sheets()
# xls默认有3个工作簿,Sheet1,Sheet2,Sheet3
rsheet = rbook.sheet_by_index(0)  # 取第一个工作簿
content = rsheet.get_rows()
phone_list = []
# 循环工作簿的所有行
for row in content:
    product_column = row[1]  # 手机号所在的列
    product_value = product_column.value  # 项目名

    #print(product_value)
    if product_value != 'memberid':  # 排除第一行
        # price_column = row[4]  # 价格所在的列
        # price_value = price_column.value
        # 打印
        phone_list.append(str(product_value).replace('.0','').split('（')[0].split('(')[0])

        #print(str(product_value).replace('.0','').split('（')[0].split('(')[0])
# print('\n')
#

# print(len(phone_list[1:]))
count = 0

# 1688gjz_all_spider
# 1688_all_spider
def url_list():
    df = pd.read_excel('C:\\Users\\Administrator\\Desktop\\合并表.xlsx', usecols=[8],names=None)
    # df = pd.read_excel('C:\\Users\\Administrator\\Desktop\\dazhong.xlsx', usecols=[3],names=None)
    # 读取项目名称列,不要列名
    df_li = df.values.tolist()
    result = []
    for s_li in df_li:
        result.append(s_li[0])
    return result


a=[]
for i in url_list():
    a.append(i)
print(a[2:4])
for mobile_no in phone_list[1:]:
    # print(mobile_no)
    if mobile_no in a:
        print(mobile_no)
    # sql_demand = "SELECT 1 FROM 1688_all_spider WHERE mobile_no='"+mobile_no+"' LIMIT 1"

    # demand = cursor.execute(sql_demand)
    # db.commit()

    # if demand > 0:
    #     print(demand)
    #     print('联系方式已存在')
        ##  去重
        # sql_demand = "update dingding_all_info_spider set is_handle='1' where mobile_no='{}'".format(mobile_no)
        # # 已发送的
        # sql_demand = "update 1688_all_spider set is_handle='1' where mobile_no='{}'".format(mobile_no)
        # demand = cursor.execute(sql_demand)
        # db.commit()
        count+=1
    # else:
        # print('不存在库')
        # pass
        #print(str(mobile_no).replace('\xa0',''))
print(count,'个重复')
cursor.close()
#
